import pymysql
from db import config
class Model:
    '''单表信息操作类'''
    tab_name = None #表名
    link = None #数据库连接对象
    cursor = None #游标对象
    pk = 'Account' #对应银行账户，这才是用户自己知道的
    fields = ['ID','Name','Accpunt','Password','Balance']#当前数据表中的字段名
    def __init__(self,table,config = config):
        '''构造方法，初始化表名，连接数据库'''
        try:
            self.tab_name = table
            self.link= pymysql.connect(host=config.Host,user = config.User,password = config.Password,db=config.DBname,port=config.Port,charset = 'utf8')
            self.cursor = self.link.cursor(pymysql.cursors.DictCursor)#返回字典格式
        except Exception as err:
            print("数据操作对象初始化失败，原因：%s"%err)
    def findAll(self):
        '''用户信息全查询'''
        try:
            sql="select * from %s"%(self.tab_name)
            print(sql)
            self.cursor.execute(sql)
            return self.cursor.fetchall()
        except Exception as err:
            print("SQL查询错误，原因：%s"%err)
            return []
    def find(self,username):
        '''用户信息查询'''
        try:
            sql = "select * from %s where  %s = %s"%(self.tab_name,self.pk,username)
            print(sql)
            self.cursor.execute(sql)
            return self.cursor.fetchone()
        except Exception as err:
            print("SQL查询错误，原因：%s"%err)
            return None
    def save(self,data={}):
        '''信息添加'''
        try:
            keys=[]
            value=[]
            for k,v in data.itmes():
                if k in self.fields:
                    keys.append(k)
                    value.append(v)
            sql = "insert into %s(%s) value(%s)"%(self.tab_name,','.join(keys),','.join(['%s']*len(value)))
            print(sql)
            self.cursor.execute(sql,tuple(value))
            self.link.commit()
            return self.cursor.lastrowid
        except Exception as err:
            print("SQL执行错误，原因：%s" % err)
            return []
    def Updata(self,data={}):
        try:
            value=[]
            for k,v in data.items():
                if (k in self.fields):
                    value.append("%s='%s'"%(k,v))
            sql = "update %s set %s where %s='%s'"%(self.tab_name,','.join(value),self.pk,data.get(self.pk))
            print(sql)
            self.cursor.execute(sql)
            self.link.commit()
            #返回影响行数
            return self.cursor.rowcount
        except Exception as err:
            print("SQL数据更新失败，原因：%s"% err)
            return []

    def __del__(self):
        '''关闭游标对象和连接对象'''
        if self.cursor !=None:
            self.cursor.close()
        if self.link!=None:
            self.link.close()
